﻿if object_id('fn_MapHTML_AssetDistributionMeter') is not null
	DROP FUNCTION fn_MapHTML_AssetDistributionMeter

GO

CREATE FUNCTION [dbo].[fn_MapHTML_AssetDistributionMeter]
(
	@MeterId int
)
RETURNS varchar(max)
AS
BEGIN
declare @html varchar(max)
set @html = 
(
	select
		 'Distribution Meter' as 'TITLE'
		,Meters.Utility_Asset_MeterId
		,STR(Meters.Latitude, 9, 9) as 'Latitude'
		,STR(Meters.Longitude, 9, 9)  as 'Longitude'
		,Meters.Date
		,Meters.Number
		,Meters.Comments
		
		-- Meter Notes
		,(select
			notes.[Name]
		  from
			tb_Utility_Asset_Meters_Notes as NotesxMeters
		  join tb_Distribution_MeterNotes notes
			on notes.MeterNoteId = NotesxMeters.Utility_Asset_MeterNoteFK
		  where
			NotesxMeters.Utility_Asset_MeterFK = @MeterId
		for xml path ('NOTE'), type) as 'NOTES'

		-- Meter Images
		,(select
			replace(replace(replace(replace(Src.SettingValue, '{0}', ImagesxMeters.Utility_Asset_Meters_ImageId), '{1}', 'MeterAssetDistribution'), '{2}', 85), '{3}', 85) as '@src'
			,replace(Href.SettingValue, '{0}', replace(replace(replace(replace(Src.SettingValue, '{0}', ImagesxMeters.Utility_Asset_Meters_ImageId), '{1}', 'MeterAssetDistribution'), '{2}', 640), '{3}', 480)) as '@href'
			,ImagesxMeters.[Filename]
		  from
			tb_Utility_Asset_Meters_Images as ImagesxMeters
		  where
			ImagesxMeters.Utility_Asset_MeterFK = @MeterId
		for xml path ('IMAGE'), type) as 'IMAGES'
	from
		tb_Utility_Asset_Meters as Meters
	left join tb_Settings as Href 
		on Href.SettingId = 25
	left join tb_Settings as Src 
		on Src.SettingId = 24
	where
		meters.Utility_Asset_MeterId = @MeterId
	for xml path ('Meter')

	)
	return @html

END



